package recomendacaoservidor.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import recomendacaoservidor.classes.Questao;
import recomendacaoservidor.classes.Questionario;
import recomendacaoservidor.classes.Servidor;

/**
 * @author Marcelo, Jefferson
 */
public class QuestaoDAO {
    
    private Connection conexao;
            
    public QuestaoDAO(Connection conexao){
        this.conexao = conexao;
    }
    
    public Questao getPrimeiraQuestao() throws SQLException{
        Questao questao = new Questao();
        String sql = "select id_questao, questao, alternativas from questoes where ordem = 1";
        Statement st = this.conexao.createStatement();
        ResultSet rs = st.executeQuery(sql);
        try{
            if(rs.next()){
                questao.setId(rs.getInt(1));
                questao.setQuestao(rs.getString(2));
                sql = "select id_categoria, categoria from categoria order by 1";
                rs = st.executeQuery(sql);
                while(rs.next()){
                    questao.addAlternativa(rs.getInt(1), rs.getString(2));
                }
            }
        }
        finally{
            if(st != null) st.close();
        }
        
        return questao;
    }
    
    public Questao getProximaQuestao(int tamanho, List<Questao> questoes) throws SQLException{
        Questao questao = new Questao();
        String sql = "select id_questao, questao, alternativas from questoes where ordem = ?";
        PreparedStatement st = this.conexao.prepareStatement(sql);
        st.setInt(1, tamanho+1);
        ResultSet rs = st.executeQuery();
        try{
            if(rs.next()){
                questao.setId(rs.getInt(1));
                questao.setQuestao(rs.getString(2));
                String alternativa = rs.getString(3);
                
                if (alternativa.equals("sistema_operacional")){
                   st = this.conexao.prepareStatement("select id_sistema, sistema_operacional from sistema_operacional order by id_sistema");
                   rs = st.executeQuery();
                }else if (alternativa.equals("aplicacoes")){
                   st = this.conexao.prepareStatement("select id_aplicacao, nome from aplicacoes where id_categoria = ?");
                   st.setInt(1, questoes.get(0).getAlternativa(0).getId());
                   rs = st.executeQuery();
                }else if (alternativa.equals("conexoes")){
                   st = this.conexao.prepareStatement("select id_conexao, descricao from conexoes order by id_conexao");
                   rs = st.executeQuery();
                }
                
                while(rs.next()){
                    questao.addAlternativa(rs.getInt(1), rs.getString(2));
                }
            }
        }
        finally{
            if(st != null) st.close();
        }
        
        return questao;
    }
    
    public int getQuantidadeQuestoes() throws SQLException {
        int quantidade = 0;
        String sql = "select count(*) from questoes";
        
        Statement st = this.conexao.createStatement();
        ResultSet rs = st.executeQuery(sql);
        if(rs.next())
            quantidade = rs.getInt(1);
        return quantidade;
     }        
    
    public Servidor getServidor(int idAplicacao){
        Servidor servidor = null;
        String sql = "select id_servidor, processador, memoria, hd from servidores where id_aplicacao = ?";
        PreparedStatement pst = null;
        try{
            pst = this.conexao.prepareStatement(sql);
            pst.setInt(1, idAplicacao);
            ResultSet rs = pst.executeQuery();
            if(rs.next())
                servidor = new Servidor(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), null);
        }
        catch(SQLException e){
            System.out.println("SQL Erro: "+e.getMessage());
        }
        catch(Exception e){
            System.out.println("Erro: "+e.getMessage());
        }
        finally{
            try{ if(pst != null) pst.close(); }catch(SQLException e){}
        }
        return servidor;
    }
    
    public String getLink(int idLink){
        String link = null;
        String sql = "select link_recomendado from conexoes where id_conexao = ?";
        PreparedStatement pst = null;
        try{
            pst = this.conexao.prepareStatement(sql);
            pst.setInt(1, idLink);
            ResultSet rs = pst.executeQuery();
            if(rs.next())
                link = rs.getString(1);
        }
        catch(SQLException e){
            System.out.println("SQL Erro: "+e.getMessage());
        }
        catch(Exception e){
            System.out.println("Erro: "+e.getMessage());
        }
        finally{
            try{ if(pst != null) pst.close(); }catch(SQLException e){}
        }
        return link;
    }
}